"""
This file will be used fill data only in stackoverflow
OMG.... UNZIP stooped working.... All my code is waste...

"""


import xml.etree.cElementTree as ET
import MySQLdb
import sys
from os import listdir
reload(sys)
sys.setdefaultencoding('utf-8')

table_type = "posthistory"
table  = table_type+"_stackoverflow_aug13"

"""

folder_path = "D:\Research\ISEC-2014\data\content\stackoverflow"
database = "isec2014"
user = "root"
password="123"
port=3306

"""

folder_path = "E:\Sangeeta\dataset\\2013_05_stack_overflow\Content\stackoverflow"
database = "sangeeta"
user = "sangeetal"
password="sangeetal"
port=3307
#"""

db1= MySQLdb.connect(host="localhost", user=user, passwd=password,db=database, port=port)
insert_cursor = db1.cursor() 


folder = listdir(folder_path)
for line in folder:
    site = line.strip() 
    print  "site=",site

    tree = ET.ElementTree(file=folder_path+"\\"+site+"\\"+table_type+".xml")
    root= tree.getroot()
    #print root.tag, root.attrib
    count = 0
    for child_of_root in root:
        #print  child_of_root.attrib
        Comment = ""
        Text = ""
        try:
            Comment=(str)(child_of_root.attrib['Comment'])
            Commebt = Comment.replace("\n"," ")
            Comment = Comment.replace("'"," ")
            Comment = Comment.replace("\""," ")
            Comment = Comment.replace(",", " ")
            Comment = Comment.replace("(", " ")
            Comment = Comment.replace(")", " ")
            Comment = Comment.replace("\\", " ")
        except Exception ,e:
            #print "error"            
            count = count+1
            #print e, "count1=", count
        
        
        try:
            Text=(str)(child_of_root.attrib['Text']).replace("\r"," ")
            Text = Text.replace("\n"," ")
            Text = Text.replace("'"," ")
            Text = Text.replace("\""," ")
            Text = Text.replace(",", " ")
            Text = Text.replace("("," ")
            Text = Text.replace(")", " ")
            Text = Text.replace("\\", " ")
        except Exception, e:
            #print "error"
            count = count+1
        try:
            UserId_or_name=(str)(child_of_root.attrib['UserId'])
        except Exception, e:
            count  =count+1 
            try:
                UserId_or_name=(str)(child_of_root.attrib['UserDisplayName'])
                UserId_or_name = UserId_or_name.replace("'"," ")
            except Exception, e:
                UserId="not given"
                count = count+1
            
        insert_str = "insert into `"+ table+ "` values("+child_of_root.attrib['Id']+","+child_of_root.attrib['PostHistoryTypeId']+","+\
        child_of_root.attrib['PostId']+",'"+child_of_root.attrib['RevisionGUID']+"','"+child_of_root.attrib['CreationDate']+"','"+\
        UserId_or_name+"','"+Comment+"','"+Text+"')"
         
        #print "insert = ", insert_str
        insert_cursor.execute(insert_str) 
        #break
    db1.commit()
    print "Number of errors = ", count
